package com.bijava.util;


import com.bijava.entity.Column;
import com.bijava.entity.JdbcEntity;
import com.bijava.entity.Table;
import com.bijava.entity.ConnectionEntity;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


/**
 * Created by ljj on 2018/6/1.
 */
public class TableFactory {

    private ConnectionEntity connectionEntity;

    public TableFactory(ConnectionEntity connectionEntity) {
        this.connectionEntity = connectionEntity;
    }


    public List<Table> getAllTables() throws SQLException {
        return isOracle() ? getOracleTableName() : getMysqlTableName();
    }

    private String getTablePrimaryKey(Table table) {
        return isOracle() ? getOrcaleTablePrimaryKey(table) : getMysqlTablePrimaryKey(table);
    }
    public List<Column> getColumn(String tableName, String url){
        return isOracle() ? getOracleColums(tableName) : getMysqlColumns(tableName, url);
    }
    private String getTablePrimaryKey(String tableName) {
        return isOracle() ? getOrcaleTablePrimaryKey(tableName) : getMysqlTablePrimaryKey(tableName);
    }
    public List<Column> getColumn(Table table, String url){
        return isOracle() ? getOracleColums(table.getName()) : getMysqlColumns(table.getName() ,url);
    }

    public List<Table> getOracleTableName() {
        List<Table> tableList = new ArrayList<>();
        String sql = "select a.TABLE_NAME TABLENAME ,b.COMMENTS \n" +
                "from user_tables a,user_tab_comments b \n" +
                "WHERE a.TABLE_NAME=b.TABLE_NAME ";
        JdbcEntity jdbcEntity = new JdbcEntity(sql, connectionEntity);
        List<Map<String, Object>> list = JDBCUtils.QueryForList(jdbcEntity);
        for (Map map : list) {
            Table table = new Table();
            table.setName(map.get("TABLENAME") + "");
            table.setComment(map.get("COMMENTS") + "");
            tableList.add(table);
        }
        return tableList;
    }

    public List<Table> getMysqlTableName() {
        List<Table> tableList = new ArrayList<>();
        String jdbcUrl = connectionEntity.getJdbcUrl();
        String dataBaseName;
        if(!jdbcUrl.contains("?")){
            dataBaseName = jdbcUrl.substring(getCharacterPosition(jdbcUrl, 3, "/") + 1);
        }else {
            dataBaseName = jdbcUrl.substring(getCharacterPosition(jdbcUrl, 3, "/") + 1, jdbcUrl.indexOf("?"));
        }
        String sql = "SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema='" + dataBaseName + "'";
        JdbcEntity jdbcEntity = new JdbcEntity(sql, connectionEntity);
        List<Map<String, Object>> list = JDBCUtils.QueryForList(jdbcEntity);
        for (Map map : list) {
            Table table = new Table();
            table.setName(map.get("TABLE_NAME") + "");
            table.setComment(map.get("TABLE_COMMENT") + "");
            tableList.add(table);
        }
        return tableList;
    }

    public boolean isOracle() {
        return connectionEntity.getDriverClass().indexOf("oracle") != -1;
    }


    public String getOrcaleTablePrimaryKey(Table table) {
        String sql = " select  col.* \n" +
                " from user_constraints con,user_cons_columns col \n" +
                " where \n" +
                " con.constraint_name=col.constraint_name and con.constraint_type='P' \n" +
                " and col.table_name='" + table.getName() + "'";
        JdbcEntity jdbcEntity = new JdbcEntity(sql, connectionEntity);
        Map<String, Object> map = (Map<String, Object>) JDBCUtils.QueryForOne(jdbcEntity);

        return map!=null?map.get("COLUMN_NAME")+"":"";
    }


    public String getMysqlTablePrimaryKey(Table table) {
        String sql = " SELECT  DISTINCT \n" +
                "  c.COLUMN_NAME  \n" +
                "FROM  \n" +
                "  INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,  \n" +
                "  INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c,  \n" +
                "  information_schema.TABLES AS ts  \n" +
                "WHERE  \n" +
                "  t.TABLE_NAME = c.TABLE_NAME  \n" +
                " AND t.TABLE_NAME='" + table.getName() + "' \n" +
                "  AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'";
        JdbcEntity jdbcEntity = new JdbcEntity(sql, connectionEntity);
        Map<String, Object> map = (Map<String, Object>) JDBCUtils.QueryForOne(jdbcEntity);
        return map!=null?map.get("COLUMN_NAME")+"":"";
    }

    public String getMysqlTablePrimaryKey(String tableName) {
        Table table = new Table();
        table.setName(tableName);
        return getMysqlTablePrimaryKey(table);
    }

    public String getOrcaleTablePrimaryKey(String tableName) {
        Table table = new Table();
        table.setName(tableName);
        return getOrcaleTablePrimaryKey(table);
    }

    public List<Column> getMysqlColumns(String table, String url) {
        int startIndex = url.lastIndexOf("/");
        int endIndex = url.indexOf("?");
        endIndex = endIndex > 0 ? endIndex : url.length();
        String schema = url.substring(startIndex+1, endIndex);

        List<Column> listColumn = new ArrayList<>();
        String sql = "select * from information_schema.columns where TABLE_SCHEMA='"+schema+"' and table_name='" + table + "'";
        JdbcEntity jdbcEntity = new JdbcEntity(sql, connectionEntity);
        List<Map<String, Object>> list = JDBCUtils.QueryForList(jdbcEntity);
        String PrimaryKey = getTablePrimaryKey(table);
        for (Map map : list) {
            Column column = new Column(table);
            String columnName = map.get("COLUMN_NAME") + "";
            if (!columnName.isEmpty() && columnName.equals(PrimaryKey)) {
                column.setIsPK("true");
            }
            column.setColumnName(columnName);

            Object maximumLength = map.get("CHARACTER_MAXIMUM_LENGTH");
            if(null != maximumLength){
                long i=Long.parseLong(maximumLength.toString());
                column.setMaxNum(i);
            }
            String dataTypeStr;
            Object dataType = map.get("DATA_TYPE");
            //适配mysql8.0及以上版本
            if (dataType instanceof byte[]){
                dataTypeStr = new String((byte[])dataType);
            }else{
                dataTypeStr = dataType.toString();
            }
            column.setSqlType(SqlTypeUtil.sqlTypeToMyBaitSqlType(dataTypeStr));
            column.setComment(map.get("COLUMN_COMMENT") + "");
            column.setJavaType(SqlTypeUtil.mysqlTypeToJavaType((dataTypeStr)));
            column.setJavaColumName(StringUtil.camelName(map.get("COLUMN_NAME") + ""));
            listColumn.add(column);
        }
        return listColumn;
    }


    public List<Column> getOracleColums(String table) {
        List<Column> listColumn = new ArrayList<Column>();
//        String sql = "SELECT DISTINCT B.COLUMN_NAME COLUMN_NAME ,\n" +
//                "B.DATA_TYPE DATA_TYPE ,\n" +
//                "A.COMMENTS COMMENTS \n" +
//                "FROM USER_COL_COMMENTS A, ALL_TAB_COLUMNS B \n" +
//                "WHERE A.TABLE_NAME = B.TABLE_NAME \n" +
//                "AND a.column_name = b.COLUMN_NAME \n" +
//                "AND A.TABLE_NAME = '" + table + "' \n" +
//                "AND a.column_name = b.COLUMN_NAME";

//        String sql = "SELECT  b.COLUMN_NAME,b.DATA_TYPE,a.COMMENTS \n" +
//                     " FROM \n" +
//                      " USER_TAB_COLUMNS b,USER_COL_COMMENTS a \n"+
//                      " WHERE \n"+
//                    "b.TABLE_NAME = '" + table + "'"+
//                    "AND b.TABLE_NAME = a.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME";
        String sql="SELECT\n" +
                "T.column_name AS column_name, \n" +
                "T.column_type AS data_type, \n" +
                "T.data_length AS data_length, \n" +
                "T.data_scale AS data_scale, \n" +
                "T.column_comment AS comments,\n" +
                "T.DATA_PRECISION AS data_precision\n" +
                "FROM\n" +
                "(\n" +
                "SELECT\n" +
                "UB.tablespace_name AS database_name,\n" +
                "UTC.table_name AS table_name,\n" +
                "UTC.column_name AS column_name,\n" +
                "UTC.data_length AS data_length,\n" +
                "UTC.data_type AS column_type,\n" +
                "utc.data_scale AS data_scale,\n" +
                "ucc.comments AS column_comment,\n" +
                "utc.column_id,\n" +
                "utc.DATA_PRECISION,\n" +
                "utc.nullable\n" +
                "FROM\n" +
                "user_tables ub\n" +
                "LEFT JOIN user_tab_columns utc ON ub.table_name = UTC.table_name\n" +
                "LEFT JOIN user_col_comments ucc ON utc.column_name = ucc.column_name\n" +
                "AND utc.table_name = ucc.table_name\n" +
                ") T\n" +
                "LEFT JOIN (\n" +
                "SELECT\n" +
                "UCC.table_name AS table_name,\n" +
                "ucc.column_name AS column_name \n" +
                "FROM\n" +
                "user_cons_columns ucc\n" +
                "LEFT JOIN user_constraints uc ON UCC.constraint_name = UC.constraint_name\n" +
                "GROUP BY\n" +
                "UCC.table_name,\n" +
                "ucc.column_name\n" +
                ") b ON T.table_name = b.TABLE_NAME\n" +
                "AND T.column_name = b.column_name\n" +
                "where T.table_name= '"+ table +"'\n"+
                "order by T.column_id";
        JdbcEntity jdbcEntity = new JdbcEntity(sql, connectionEntity);
        List<Map<String, Object>> list = JDBCUtils.QueryForList(jdbcEntity);
        String PrimaryKey = getTablePrimaryKey(table);
        for (Map map : list) {
            Column column = new Column(table);
            String columnName = map.get("COLUMN_NAME") + "";
            column.setColumnName(columnName);

            column.setSqlType(SqlTypeUtil.sqlTypeToMyBaitSqlType(map.get("DATA_TYPE")+ "") );

            int dataScale=0;
            int dataLength=0;
            if(null!=(map.get("DATA_SCALE"))){
                int i=Integer.parseInt( map.get("DATA_SCALE")+"");
                column.setDataScale(i);
                dataScale+=i;
            }
            if(null!=(map.get("DATA_PRECISION"))){
                long i=Long.parseLong( map.get("DATA_PRECISION")+"");
                column.setMaxNum(i);
                dataLength+=i;
            }
            String dataType=(map.get("DATA_TYPE") + "");
            column.setJavaType(SqlTypeUtil.oracleSqlTypeToJavaType(dataType,dataLength,dataScale));
            column.setComment(map.get("COMMENTS") + "");
            column.setJavaColumName(StringUtil.camelName(map.get("COLUMN_NAME") + ""));
            if (!"null".equals(columnName)&&!columnName.isEmpty() && columnName.equals(PrimaryKey)) {
                column.setIsPK("true");
               listColumn.add(column);
            }else {
                listColumn.add(column);
            }


        }
        return listColumn;
    }


    public static int getCharacterPosition(String string, int i, String character) {
        Matcher slashMatcher = Pattern.compile(character).matcher(string);
        int mIdx = 0;
        while (slashMatcher.find()) {
            mIdx++;
            //当"/"符号第三次出现的位置
            if (mIdx == i) {
                break;
            }
        }
        return slashMatcher.start();
    }
}
